﻿using System;
using System.IO;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace TestDB2Excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        void SetSytle(IWorkbook workbook,ISheet sheet,int rowIndex)
        {
            ICellStyle style3 = workbook.CreateCellStyle();
            style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style3.VerticalAlignment = VerticalAlignment.Center;
            style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
            style3.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
            style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle style4 = workbook.CreateCellStyle();
            style4.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style4.VerticalAlignment = VerticalAlignment.Center;
            style4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style4.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
            style4.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            IRow row1 = sheet.CreateRow(rowIndex);
            for (int j = 0; j < 4; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(4).CellStyle = style4;
            for (int j = 5; j < 18; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(18).CellStyle = style4;
            for (int j = 19; j < 20; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(20).CellStyle = style4;
            for (int j = 21; j < 22; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(22).CellStyle = style4;
            for (int j = 23; j < 34; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(34).CellStyle = style4;
            for (int j = 35; j < 59; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(59).CellStyle = style4;
            for (int j = 60; j < 64; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(64).CellStyle = style4;
            for (int j = 65; j < 66; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(66).CellStyle = style4;
            for (int j = 67; j < 68; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(68).CellStyle = style4;
            for (int j = 69; j < 70; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(70).CellStyle = style4;
            for (int j = 71; j < 72; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(72).CellStyle = style4;
            for (int j = 73; j < 74; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(74).CellStyle = style4;
            for (int j = 75; j < 76; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(76).CellStyle = style4;
            for (int j = 77; j < 84; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(84).CellStyle = style4;
            for (int j = 85; j < 90; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(90).CellStyle = style4;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string fileTemp = System.Reflection.Assembly.GetExecutingAssembly().Location;
            string file2 = Path.Combine(Path.GetDirectoryName(fileTemp), @"xls\模板.xls");
            string filepath = @"c:\1234.xls";
            if (File.Exists(filepath))
                File.Delete(filepath);
                File.Copy(file2, filepath);
            if (File.Exists(filepath))
            {
                FileStream file;
                ISheet sheet;
                IWorkbook workbook = null;
                try
                {
                    file = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    //string filepath = @"C:\Users\53572\Desktop\测试.xlsx";
                    // 2007版本及以上版本
                    if (filepath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(file);
                    // 2003版本
                    else if (filepath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(file);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheet("标准目录");
                        sheet.ShiftRows(6, 6, 10);
                        CellRangeAddress region1 = new CellRangeAddress(6, 6, 0, 4);//P/#			
                        sheet.AddMergedRegion(region1);
                        CellRangeAddress region2 = new CellRangeAddress(6, 6, 5, 18);// 图号(DRAWING No)
                        sheet.AddMergedRegion(region2);
                        CellRangeAddress region3 = new CellRangeAddress(6, 6, 19, 20);// TP1
                        sheet.AddMergedRegion(region3);
                        CellRangeAddress region4 = new CellRangeAddress(6, 6, 21, 22);// TP2
                        sheet.AddMergedRegion(region4);
                        CellRangeAddress region5 = new CellRangeAddress(6, 6, 23, 34);//名称 （NAME）
                        sheet.AddMergedRegion(region5);
                        CellRangeAddress region6 = new CellRangeAddress(6, 6, 35, 59);//规格(TYPE)
                        sheet.AddMergedRegion(region6);
                        CellRangeAddress region7 = new CellRangeAddress(6, 6, 60, 64);//分组号
                        sheet.AddMergedRegion(region7);
                        CellRangeAddress region8 = new CellRangeAddress(6, 6, 65, 66);//G01
                        sheet.AddMergedRegion(region8);
                        CellRangeAddress region9 = new CellRangeAddress(6, 6, 67, 68);//G02
                        sheet.AddMergedRegion(region9);
                        CellRangeAddress region10 = new CellRangeAddress(6, 6, 69, 70);//G03
                        sheet.AddMergedRegion(region10);
                        CellRangeAddress region11 = new CellRangeAddress(6, 6, 71, 72);//G04
                        sheet.AddMergedRegion(region11);
                        CellRangeAddress region12= new CellRangeAddress(6, 6, 73, 74);//G05
                        sheet.AddMergedRegion(region12);
                        CellRangeAddress region13 = new CellRangeAddress(6, 6, 75, 76);//G06
                        sheet.AddMergedRegion(region13);
                        CellRangeAddress region14 = new CellRangeAddress(6, 6, 77, 84);//制造商
                        sheet.AddMergedRegion(region14);
                        CellRangeAddress region15 = new CellRangeAddress(6, 6, 85, 90);//备注
                        sheet.AddMergedRegion(region15);

                        SetSytle(workbook, sheet,6);
                        SetSytle(workbook, sheet, 7);
                        sheet.GetRow(1).GetCell(0).SetCellValue(@"小松（大连）机床有限公司");
                        file = new FileStream(filepath, FileMode.Open, FileAccess.Write);
                        workbook.Write(file);
                        file.Close();
                        workbook.Close();
                    }
                }
                catch (System.Exception ex)
                {
                    throw ex;
                }
            }
        }
    }
}
